package live.ifox.helper;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

import live.ifox.entity.Record;

public class DBHelper extends SQLiteOpenHelper {
    private final String table = "memo";
    private static DBHelper instance;

    public static DBHelper getInstance(Context context, String dbName, int version) {
        if (instance == null) {
            instance = new DBHelper(context, dbName, version);
        }
        return instance;
    }

    private DBHelper(Context context, String dbName,
                     int version) {
        super(context, dbName, null, version);
    }

    @Override
    //数据库第一次创建时被调用
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE memo(uid TEXT PRIMARY KEY," +
                "category TEXT,date_ TEXT,detail TEXT)");
    }

    //软件版本号发生改变时调用
    //在 SQLite 中，除了重命名表和在已有的表中添加列，ALTER TABLE 命令不支持其他操作
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // db.execSQL("ALTER TABLE user RENAME TO user_old");
    }

    public void addItem(Record item) {
        /*
        getWritableDatabase().execSQL("insert into memo values (?,?,?,?)",
                new String[]{item.getUid(), item.getCategory(), item.getDate_(), item.getDetail()});
        */

        ContentValues row = new ContentValues();
        row.put("uid", item.getUid());
        row.put("category", item.getCategory());
        row.put("date_", item.getDate_());
        row.put("detail", item.getDetail());
        getWritableDatabase().insert(table, null, row);
    }

    public void update(Record item) {
        /*
        getWritableDatabase().execSQL("update memo set category = ?,date_ = ?,detail = ? where uid = ?",
                new String[]{item.getCategory(), item.getDate_(), item.getDetail(), item.getUid()});
       */
        ContentValues row = new ContentValues();
        row.put("category", item.getCategory());
        row.put("date_", item.getDate_());
        row.put("detail", item.getDetail());
        getWritableDatabase().update(table, row, "uid=?",
                new String[]{item.getUid()});
    }

    public void remove(String uid) {
        getWritableDatabase().delete(table, "uid=?",
                new String[]{uid});
    }

    public List<String> getCategories() {
        List<String> items = new ArrayList<>();
        Cursor cursor = getReadableDatabase().rawQuery("select category from memo group by category", null);
        while (cursor.moveToNext()) {
            items.add(cursor.getString(0));
        }
        cursor.close();
        return items;
    }

    public List<Record> getItemsByCategory(String category) {
        List<Record> items = new ArrayList<>();
        Cursor cursor = getReadableDatabase().rawQuery("select * from memo where category=?",
                new String[]{category});
        while (cursor.moveToNext()) {
            Record item = new Record();
            item.setUid(cursor.getString(0));
            item.setCategory(cursor.getString(1));
            item.setDate_(cursor.getString(2));
            item.setDetail(cursor.getString(3));
            items.add(item);
        }
        cursor.close();
        return items;
    }

    public List<Record> getAll() {
        List<Record> items = new ArrayList<>();
        Cursor cursor = getReadableDatabase().rawQuery("select * from memo", null);
        while (cursor.moveToNext()) {
            Record item = new Record();
            item.setUid(cursor.getString(0));
            item.setCategory(cursor.getString(1));
            item.setDate_(cursor.getString(2));
            item.setDetail(cursor.getString(3));
            items.add(item);
        }
        cursor.close();
        return items;
    }

    public Record getItem(String uid) {
        Record item = new Record();
        Cursor cursor = getReadableDatabase().rawQuery("select * from memo where uid=?",
                new String[]{uid});
        if (cursor.moveToFirst()) {
            item.setUid(cursor.getString(0));
            item.setCategory(cursor.getString(1));
            item.setDate_(cursor.getString(2));
            item.setDetail(cursor.getString(3));
        }
        cursor.close();
        return item;
    }
}
